<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Services_model extends CI_Model 
{
	function __construct()
	{
		parent:: __construct();
	}

	/*********************************************************
	**	MODULE SERVICES 									**
	**	AUTHOR 			: ANDI SISWANTO						**
	**	DATE CREATED	: 25 September 2014					**
	**	MODIFIED DATE	: 25 SEPTEMBER 2014					**
	**	MODIFIED BY		: Andi Siswanto						**
	*********************************************************/
    function getServPagFloor()
	{ 
        $sql = "
            select distinct flo.mst_floor_desc 
            from fm_mst_floor flo 
            where flo.mst_floor_status = 'Y' 
                and mst_floor_desc != '15' 
            order by mst_floor_desc asc
        ";
        $query = $this->db->query($sql);
        return $query->result_array();
	}
    
    //ORDER EQUIPMENT START=======================================================================================================================
    function getOrderIdOld(){
        $sql = "
            select max(to_number(substr(order_id,1,5),'99999')) order_id_old, rtrim(to_char(sysdate,'RM')) romawi 
            from FM_ORDER_HEADER
        ";
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function getOrderIdNext($cOrderIdNew,$cRomawi){
        $sql = "
            select lpad($cOrderIdNew,5,0)||'/ORDR/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') ORDER_ID 
            from dual
        ";
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function orderSelect1(){
        $sql 	= "
            select order_det_id
            from fm_order_detail
            where rownum=1
            order by order_det_id desc  
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function insertOrderHeader($arr){
        $pOrderId    		=  $arr['pOrderId'];
        $pHrId   			=  $arr['pHrId'];
        $pDesc    			=  $arr['pDesc'];
        $pTglAwal    		=  $arr['pTglAwal'];
        $pTglAkhir    		=  $arr['pTglAkhir'];
        $pOrderFor    		=  $arr['pOrderFor'];
        $pAtasanId    	    =  $arr['pAtasanId'];
        $pAtasanApp   		=  $arr['pAtasanApp'];
        $pJumlahItem   		=  $arr['pJumlahItem'];
        $pItemCek    		=  $arr['pItemCek'];
        $pItemId    		=  $arr['pItemId'];
        $pItemName    	    =  $arr['pItemName'];
        $pItemQty   		=  $arr['pItemQty'];
        $pOtherId    	    =  $arr['pOtherId'];
        $pOtherName    		=  $arr['pOtherName'];
        /* ORI
        $sql = "
            insert into FM_ORDER_HEADER (order_id, hr_id, order_description, 
    	           order_tgl_awal, order_tgl_akhir, 
    		      order_date, order_for, ORDER_ATASAN_ID, ORDER_ATASAN_APP, ORDER_ATASAN_APP_DATE) 
            values 
    		      ('$pOrderID', '$nik', '$pOrderDesc', 
    		      to_date('$pOrderTgl1','dd/mm/yyyy'), to_date('$pOrderTgl2','dd/mm/yyyy'),
    		      trunc(sysdate), '$pNikFor', '$pNikFor', 'Y',SYSDATE) 
        ";
        */
        $sql = "
            insert into FM_ORDER_HEADER (order_id, hr_id, order_description, 
    	           order_tgl_awal, order_tgl_akhir, 
    		      order_date, order_for, ORDER_ATASAN_ID, ORDER_ATASAN_APP, ORDER_ATASAN_APP_DATE) 
            values 
    		      ('$pOrderId', '$pHrId', '$pDesc', 
    		      to_date('$pTglAwal','dd/mm/yyyy'), to_date('$pTglAkhir','dd/mm/yyyy'),
    		      trunc(sysdate), '$pOrderFor', '$pAtasanId', 'Y',SYSDATE) 
        ";
        echo $sql; //exit;
        $query = $this->db->query($sql);
        
    }
    function insertOrderItem($pOrderId,$pWareItemId,$pWareItemName,$pQty,$nextId){  
        /*set order_id='$pOrderId', 
                order_det_id='$pDesc', 
                warehouse_name='$pTglAwal',
                warehouse_id='$pTglAkhir',
                order_det_qty=trunc(sysdate), 
                order_det_STATUS='$pOrderFor'*/
        $sql 	= "
            insert into fm_order_detail 
			     (order_id, order_det_id,warehouse_name, warehouse_id, order_det_qty, order_det_STATUS) 
            values 
			     ('$pOrderId', '$nextId', '$pWareItemName', '$pWareItemId', '$pQty',  'Y') 
        ";    
        echo $sql; //exit;         
        $query = $this->db->query($sql);
    }
    function insertOrderItemOther($pOrderId,$pOtherName,$nextId){  
        /*set order_id='$pOrderId', 
                order_det_id='$pDesc', 
                warehouse_name='$pTglAwal',
                warehouse_id='$pTglAkhir',
                order_det_qty=trunc(sysdate), 
                order_det_STATUS='$pOrderFor'*/
        $sql 	= "
            insert into fm_order_detail 
    		  (order_id, order_det_id,warehouse_name, warehouse_id,order_det_qty, order_det_STATUS) 
            values 
    		  ('$pOrderId', '$nextId','$pOtherName', '00004/DOC/XII/2008',0,'Y') 
        ";    
        echo $sql; //exit;         
        $query = $this->db->query($sql);
    }
    //ORDER EQUIPMENT END=========================================================================================================================
    
    //SARAN KELUHAN START=======================================================================================================================
    function getSarKelIdOld(){
        $sql = "
            select max(to_number(substr(pobox_id,1,5),'99999')) pobox_id_old, rtrim(to_char(sysdate,'RM')) romawi 
            from fm_pobox
        ";
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function getSarKelIdNext($cPoboxIdNew,$cRomawi){
        $sql = "
            select lpad($cPoboxIdNew,5,0)||'/POBX/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') pobox_id 
            from dual
        ";
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function insertSarKel($arr){
        //$pCType      		=  $arr['pCType'];
        $pPoboxId    		=  $arr['pPoboxId'];
    	$pNik    			=  $arr['pNik'];
    	$pType    			=  $arr['pType'];
    	$pCriteria    		=  $arr['pCriteria'];
    	$pSubject    		=  $arr['pSubject'];
    	$pDescription  		=  $arr['pDescription'];
    	$pAtasanNik    		=  $arr['pAtasanNik'];
    	$pAtasanNama    	=  $arr['pAtasanNama'];
    	$pNikFor    		=  $arr['pNikFor'];
    	$pRelatedTo    		=  $arr['pRelatedTo'];
        //KENAPA ATASAN NIK MESTI 79270
        $sql 	= "
            insert into fm_pobox
                (pobox_id, pobox_nip, pobox_untuk_nik, pobox_type, pobox_subject, pobox_description, type_id, pobox_atasan_nik, pobox_atasan_app, pobox_atasan_app_date, pobox_date,  pobox_reference, pobox_relatedto) 
            values 
                ('$pPoboxId','$pNik','$pNikFor','$pCriteria','$pSubject','$pDescription','$pType', '79270','Y',sysdate, sysdate, '$pReqId','$pRelatedTo')
        ";    
        echo $sql; //exit;         
        $query = $this->db->query($sql);
        
    }
    //SARAN KELUHAN END=========================================================================================================================
    
    //PAGINIG INFO START=======================================================================================================================
    function getPagingIdOld(){
        $sql = "
            select max(to_number(substr(info_id,1,5),'99999')) info_id_old, rtrim(to_char(sysdate,'RM')) romawi 
            from fm_paging_info
        ";
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function getPagingIdNext($cInfoIdNew,$cRomawi){
        $sql = "
            select lpad($cInfoIdNew,5,0)||'/INFO/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') info_id 
            from dual
        ";
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function insertPaging($arr){
        $pInfoId    	=  $arr['pInfoId'];
        $pNik    		=  $arr['pNik'];
        $pSdate    		=  $arr['pSdate'];
        $pShour    		=  $arr['pShour'];
        $pSminute    	=  $arr['pSminute'];
        $pRepetition    =  $arr['pRepetition'];
        $pRepetitionJumlah=  $arr['pRepetitionJumlah'];
        $pRepetitionTiap=  $arr['pRepetitionTiap'];
        $pKeterangan    =  $arr['pKeterangan'];
        $pSubject    	=  $arr['pSubject'];
        $pDescription  	=  $arr['pDescription'];
        $pAtasanNik    	=  $arr['pAtasanNik'];
        $pAtasanNama    =  $arr['pAtasanNama'];
        $pNikFor    	=  $arr['pNikFor'];
        //$pReqId    		=  $arr['pReqId'];
        //$pReqDetId    	=  $arr['pReqDetId'];
        $pAccessControlRow  = $arr['pAccessControlRow'];
        $pFloorCek          =  $arr['pFloorCek'];
        $pFloorName         =  $arr['pFloorName'];
        //KENAPA ATASAN NIK MESTI 79270
        $sql 	= "
            insert into fm_paging_info
                (info_id, info_nik, info_untuk_nik, info_sdate, info_subject, info_description, info_atasan_nik, info_reference, info_shour, info_sminute, info_repetition, info_keterangan, info_repetition_jumlah, info_repetition_tiap) 
            values 
                ('$pInfoId','$pNik','$pNikFor',to_date('$pSdate','dd/mm/yyyy'),'$pSubject','$pDescription','$pAtasanNik','$pReqId','$pShour','$pSminute','$pRepetition','$pKeterangan', '$pRepetitionJumlah','$pRepetitionTiap')
        ";    
        echo $sql; //exit;         
        $query = $this->db->query($sql);
        
    }
    function pagingSelect1(){
        $sql 	= "
            select info_floor_id
            from FM_PAGING_INFO_FLOOR
            where rownum=1
            order by info_floor_id desc  
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function insertPagingFloor($nextNomer,$pCtrlCardFloor,$xInfoId){
        $sql 	= "
            INSERT INTO FM_PAGING_INFO_FLOOR
                (INFO_FLOOR_ID, INFO_FLOOR_NAME, INFO_ID) 
            VALUES 
                ('$nextNomer', '$pCtrlCardFloor, '$xInfoId')
        ";    
        echo $sql; //exit;         
        $query = $this->db->query($sql);
        
    }
    //PAGING INFO END==========================================================================================================================
    
    //TRAFFIC START==========================================================================================================================
    function getDataTraffic($pSerialNo){
        $sql 	= "
            select traf.TRAFFIC_USER_NAME,
    			   traf.TRAFFIC_USER_TYPE,
    			   traf.TRAFFIC_VENDOR_NAME,
    			   to_char(traf.TRAFFIC_SDATE,'dd/mm/yyyy') as TRAFFIC_SDATE,
    			   to_char(traf.TRAFFIC_EDATE,'dd/mm/yyyy') as TRAFFIC_EDATE,
    			   traf.TRAFFIC_SHOUR,
    			   traf.TRAFFIC_SMINUTE,
    			   traf.TRAFFIC_EHOUR,
    			   traf.TRAFFIC_EMINUTE,
    			   traf.TRAFFIC_STATUS,
    			   traf.TRAFFIC_DESC,
    			   traf.TRAFFIC_FLOOR,
    			   traf.TRAFFIC_ID,
    			   trunc(TRAFFIC_SDATE) as PDATE1,
    			   trunc(TRAFFIC_EDATE) as PDATE2,
    			   traf.TRAFFIC_OPEN as PSTATUS1,
    			   traf.TRAFFIC_CLOSE as PSTATUS2
    		from FM_TRAFFIC traf
    		where traf.TRAFFIC_SERIAL = '$pSerialNo'
    		and traf.TRAFFIC_MAX_ID = (SELECT max(traf.TRAFFIC_MAX_ID)
    								   FROM fm_traffic traf
    								   WHERE traf.traffic_serial = '$pSerialNo') 
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function getDataSik($pSerialNo){
        $sql 	= "
            select (
                select mst.VENDOR_NAME
		        from LKK_VENDOR_MST mst
  		        where mst.VENDOR_ID = sik.VENDOR_ID
            )VENDOR_NAME,
    		      jb.SIKJ_NAME,
    		      'Vendor' as vendor_type
    		from FM_SIK sik 
            inner join FM_SIK_JOB jb
  		        on sik.SIK_ID = jb.SIK_ID
    		where jb.SIKJ_SN = '$pSerialNo' 
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function getDataTrafficFloor(){
        $sql 	= "
            select distinct flo.mst_floor_desc
			from fm_mst_floor flo
			where flo.mst_floor_status = 'Y'
			order by flo.mst_floor_desc
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function getCountDetItem($aTrID){
        $sql 	= "
            select count(*) COUNT_DET_ITEM
			from FM_TRAFFIC_DET trafdet 
			where trafdet.TRAFFIC_ID = '$aTrID'
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function getTrafDet($aTrID){
        $sql 	= "
            select trafdet.TRAFFIC_DET_ID,
                trafdet.TRAFFIC_DET_TYPE,
    			trafdet.TRAFFIC_DET_SERIAL#,
    			trafdet.TRAFFIC_DET_QTY
			from FM_TRAFFIC_DET trafdet 
			where trafdet.TRAFFIC_ID = '$aTrID'
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    
    function getTrafOldId(){
        $sql 	= "
            select max(to_number(substr(traffic_id,1,5),'99999')) traffic_id_old, rtrim(to_char(sysdate,'RM')) romawi 
            from FM_TRAFFIC
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function getTrafNextId($cNoTrafficIdNew,$cRomawi){
        $sql 	= "
            select lpad($cNoTrafficIdNew,5,0)||'/TRAFF/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') TRAFFIC_ID 
            from dual
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function maxSerialId(){
        $sql 	= "
            SELECT max(traf.TRAFFIC_MAX_ID) as MAXI
    		FROM fm_traffic traf
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function insertTraffic($arr,$nextId,$maxId){
        $pTrID    	=  $arr['pTrID'];
    	$pTrUserName 	= $arr['pTrUserName'];
    	$pTrUserType 	= $arr['pTrUserType'];
    	$pTrVendorName 	= $arr['pTrVendorName'];
    	$pTrStartDate 	= $arr['pTrStartDate'];
    	$pTrEndDate 	= $arr['pTrEndDate'];
    	$pTrStartHour 	= $arr['pTrStartHour'];
    	$pTrStartMinute = $arr['pTrStartMinute'];
    	$pTrEndHour 	= $arr['pTrEndHour'];
    	$pTrEndMinute 	= $arr['pTrEndMinute'];
    	$pTrDesc 	= $arr['pTrDesc'];
    	$pTrFloor	= $arr['pTrFloor'];
    	$pTrStatus	= $arr['pTrStatus'];
    	//$pTrAtasanNik	= $arr['pAtasanNik'];
    	$pTrSerial	= $arr['pTrSerial'];
    	$pRow  		=  $arr['pRow'];	
    	$pTrdID		=  $arr['pTrdID'];
    	$pTrdQty	=  $arr['pTrdQty'];
    	$pTrdSerial 	=  $arr['pTrdSerial'];
        $sql 	= "
            insert into FM_TRAFFIC traf 
                (traf.TRAFFIC_ID, traf.TRAFFIC_USER_NAME,
                    traf.TRAFFIC_USER_TYPE, traf.TRAFFIC_VENDOR_NAME,
                    traf.TRAFFIC_SDATE, traf.TRAFFIC_EDATE,
                    traf.TRAFFIC_SHOUR, traf.TRAFFIC_SMINUTE,
                    traf.TRAFFIC_EHOUR, traf.TRAFFIC_EMINUTE,
                    traf.TRAFFIC_DESC, traf.TRAFFIC_FLOOR,
                    traf.TRAFFIC_STATUS, traf.TRAFFIC_OPEN, traf.TRAFFIC_SERIAL, traf.TRAFFIC_MAX_ID) 
            values
                ('$nextId','$pTrUserName',
    		      '$pTrUserType','$pTrVendorName',
    		      to_date('$pTrStartDate','dd/mm/yyyy'), to_date('$pTrEndDate','dd/mm/yyyy'),
    		      '$pTrStartHour','$pTrStartMinute',
    		      '$pTrEndHour', '$pTrEndMinute',
    		      '$pTrDesc', '$pTrFloor',
		          '$pTrStatus', '1', '$pTrSerial', '$maxId')
        ";    
        echo $sql; //exit;         
        $query = $this->db->query($sql);
    }
    function getTrafDetOldId(){
        $sql 	= "
            select max(to_number(substr(traffic_det_id,1,5),'99999')) traffic_det_id_old, rtrim(to_char(sysdate,'RM')) romawi 
            from FM_TRAFFIC_DET
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function getTrafDetNextId($cNoTrafficDetIdNew,$cRomawi){
        $sql 	= "
            select lpad($cNoTrafficDetIdNew,5,0)||'/TRAFD/$cRomawi/'||TO_CHAR(SYSDATE,'YYYY') TRAFFIC_DET_ID 
            from dual
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function insertTrafficDet($cNoTrafficDetID,$pTrdQty,$pTrdSerial,$cNoTrafficID){
        $sql 	= "
            insert into fm_traffic_det 
                (traffic_det_id, traffic_det_qty, traffic_det_serial#, traffic_id) 
            values
                ('$cNoTrafficDetID', $pTrdQty, '$pTrdSerial', '$cNoTrafficID')
        ";    
        echo $sql; //exit;         
        $query = $this->db->query($sql);
    }
    function updateTrafficDet($pTrdQty,$pTrdSerial,$cNoTrafficID,$pTrdID){
        $sql 	= "
            update fm_traffic_det set traffic_det_qty = $pTrdQty, 
    		      traffic_det_serial# = '$pTrdSerial'
    		where traffic_id = '$cNoTrafficID'
    		      and traffic_det_id = '$pTrdID'
        ";    
        echo $sql; //exit;         
        $query = $this->db->query($sql);
    }
    function updateTraffic($pTrEndHour,$pTrEndMinute,$pTrID){
        $sql 	= "
            update fm_traffic traf set traf.TRAFFIC_EDATE = to_date(sysdate,'dd/mm/yyyy'),
        		traf.TRAFFIC_EHOUR = '$pTrEndHour',
        		traf.TRAFFIC_EMINUTE = '$pTrEndMinute',
        		traf.TRAFFIC_CLOSE = '1'
    		where traf.TRAFFIC_ID = '$pTrID'
        ";    
        echo $sql; //exit;         
        $query = $this->db->query($sql);
    }
    //TRAFFIC END============================================================================================================================
    
    //MONITORING START==========================================================================================================================
    function getDataMonitoringCount($pPrNo){
        $sql 	= "
            select count(*) CNT_ROW 
			from lkk_fo_det a, lkk_pr_mst b, lkk_pr_det c 
			where a.prm_requisition_id = b.prm_requisition_id 
			  and a.vendor_id = b.vendor_id 
			  and a.vendor_site_id = b.vendor_site_id
			  and a.prm_requisition_id = c.prm_requisition_id
			  and a.vendor_id = c.vendor_id
			  and a.prm_requisition_id = '$pPrNo'
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    function getDataMonitoring($pPrNo){
        $sql 	= "
            select fom_id, prm_requisition_id, vendor_id, 
				   vendor_site_id, prm_last_update_by, prm_last_update_date, 
				   prd_dmr_status, prd_det_id, prd_item_description, 
				   prd_uom, prd_line_num, prd_quantity, 
				   prd_unit_price, prd_total_price, prd_need_by_date, 
				   prd_item_status_by, expired 
			from ( 
    			select rownum recnum, p.fom_id, p.prm_requisition_id, 
    				   p.vendor_id, p.vendor_site_id, p.prm_last_update_by, 
    				   p.prm_last_update_date, p.prd_dmr_status, 
    				   p.prd_det_id, p.prd_item_description, p.prd_uom, 
    				   p.prd_line_num, p.prd_quantity, p.prd_unit_price, p.prd_total_price, 
    				   p.prd_need_by_date, p.prd_item_status_by, p.expired 
    			from (
        			select a.fom_id, a.prm_requisition_id, a.vendor_id, a.vendor_site_id, 
        				   b.prm_last_update_by, to_char(b.prm_last_update_date,'dd/mm/yyyy hh24:mi') prm_last_update_date,
        				   c.prd_dmr_status, c.prd_det_id, c.prd_item_description, c.prd_uom, 
        				   c.prd_line_num, c.prd_quantity, c.prd_unit_price, c.prd_total_price, 
        				   c.prd_need_by_date, c.prd_item_status_by,
        				   (trunc(c.prd_need_by_date) - trunc(sysdate)) expired
        			from lkk_fo_det a, lkk_pr_mst b, lkk_pr_det c 
        			where a.prm_requisition_id = b.prm_requisition_id 
        			and a.vendor_id = b.vendor_id 
        			and a.vendor_site_id = b.vendor_site_id
        			and a.prm_requisition_id = c.prm_requisition_id
        			and a.vendor_id = c.vendor_id
        			and a.prm_requisition_id = '$pPrNo'
        			order by c.prd_need_by_date asc
     		    )p
   	         )
        ";    
        //echo $sql; //exit;         
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    //MONITORING END==========================================================================================================================
}
?>